package net.loststone.ingestion;

import net.loststone.*;
import org.apache.commons.httpclient.*;
import org.apache.commons.httpclient.methods.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import groovy.util.XmlSlurper;

class TownSlurperService {

    boolean transactional = true
    
    HttpConnectionManager connectionManager

    def stateUri = "http://www.ine.es/daco/daco42/codmun/cod_comun.htm"
    def provinceUri = "http://www.ine.es/daco/daco42/codmun/codmun10/10codmunmapa.htm"
    def townUri = "http://www.ine.es/daco/daco42/codmun/codmun10/10codmun.xls"

    def slurp() {
        slurpStates();
        slurpProvinces();
        slurpTowns();
    }

    def slurpStates() {
        def states = [];
        
        states.add(['state_id':'1', 'name':'Andalucía'])
        states.add(['state_id':'2', 'name':'Aragón'])
        states.add(['state_id':'3', 'name':'Asturias'])
        states.add(['state_id':'4', 'name':'Baleares'])
        states.add(['state_id':'5', 'name':'Canarias'])
        states.add(['state_id':'6', 'name':'Cantabria'])
        states.add(['state_id':'7', 'name':'Castilla y León'])
        states.add(['state_id':'8', 'name':'Castilla-La Mancha'])
        states.add(['state_id':'9', 'name':'Cataluña'])
        states.add(['state_id':'10', 'name':'Comunidad Valenciana'])
        states.add(['state_id':'11', 'name':'Extremadura'])
        states.add(['state_id':'12', 'name':'Galicia'])
        states.add(['state_id':'13', 'name':'Madrid'])
        states.add(['state_id':'14', 'name':'Murcia'])
        states.add(['state_id':'15', 'name':'Navarra'])
        states.add(['state_id':'16', 'name':'País Vasco'])
        states.add(['state_id':'17', 'name':'La Rioja'])
        states.add(['state_id':'18', 'name':'Ceuta'])
        states.add(['state_id':'19', 'name':'Melilla'])

        saveStates(states);
    }

    def slurpProvinces() {
        // def provinces = slurpUri(provinceUri, processProvinceFeed);
        def provinces = [];

        provinces.add(['province_id':'1', 'name':'Álava', 'state_name':'País Vasco'])
        provinces.add(['province_id':'2', 'name':'Albacete', 'state_name':'Castilla-La Mancha'])
        provinces.add(['province_id':'3', 'name':'Alicante', 'state_name':'Comunidad Valenciana'])
        provinces.add(['province_id':'4', 'name':'Almería', 'state_name':'Andalucía'])
        provinces.add(['province_id':'33', 'name':'Asturias', 'state_name':'Asturias'])
        provinces.add(['province_id':'5', 'name':'Ávila', 'state_name':'Castilla y León'])
        provinces.add(['province_id':'6', 'name':'Badajoz', 'state_name':'Extremadura'])
        provinces.add(['province_id':'7', 'name':'Baleares', 'state_name':'Baleares'])
        provinces.add(['province_id':'8', 'name':'Barcelona', 'state_name':'Cataluña'])
        provinces.add(['province_id':'9', 'name':'Burgos', 'state_name':'Castilla y León'])
        provinces.add(['province_id':'10', 'name':'Cáceres', 'state_name':'Extremadura'])
        provinces.add(['province_id':'11', 'name':'Cádiz', 'state_name':'Andalucía'])
        provinces.add(['province_id':'39', 'name':'Cantabria', 'state_name':'Cantabria'])
        provinces.add(['province_id':'12', 'name':'Castellón', 'state_name':'Comunidad Valenciana'])
        provinces.add(['province_id':'51', 'name':'Ceuta', 'state_name':'Ceuta'])
        provinces.add(['province_id':'13', 'name':'Ciudad Real', 'state_name':'Castilla-La Mancha'])
        provinces.add(['province_id':'14', 'name':'Córdoba', 'state_name':'Andalucía'])
        provinces.add(['province_id':'15', 'name':'A Coruña', 'state_name':'Galicia'])
        provinces.add(['province_id':'16', 'name':'Cuenca', 'state_name':'Castilla-La Mancha'])
        provinces.add(['province_id':'17', 'name':'Girona', 'state_name':'Cataluña'])
        provinces.add(['province_id':'18', 'name':'Granada', 'state_name':'Andalucía'])
        provinces.add(['province_id':'19', 'name':'Guadalajara', 'state_name':'Castilla-La Mancha'])
        provinces.add(['province_id':'20', 'name':'Guipúzcoa', 'state_name':'País Vasco'])
        provinces.add(['province_id':'21', 'name':'Huelva', 'state_name':'Andalucía'])
        provinces.add(['province_id':'22', 'name':'Huesca', 'state_name':'Aragón'])
        provinces.add(['province_id':'23', 'name':'Jaén', 'state_name':'Andalucía'])
        provinces.add(['province_id':'24', 'name':'León', 'state_name':'Castilla y León'])
        provinces.add(['province_id':'27', 'name':'Lugo', 'state_name':'Galicia'])
        provinces.add(['province_id':'25', 'name':'Lleida', 'state_name':'Cataluña'])
        provinces.add(['province_id':'28', 'name':'Madrid', 'state_name':'Madrid'])
        provinces.add(['province_id':'29', 'name':'Málaga', 'state_name':'Andalucía'])
        provinces.add(['province_id':'52', 'name':'Melilla', 'state_name':'Melilla'])
        provinces.add(['province_id':'30', 'name':'Murcia', 'state_name':'Murcia'])
        provinces.add(['province_id':'31', 'name':'Navarra', 'state_name':'Navarra'])
        provinces.add(['province_id':'32', 'name':'Ourense', 'state_name':'Galicia'])
        provinces.add(['province_id':'34', 'name':'Palencia', 'state_name':'Castilla y León'])
        provinces.add(['province_id':'35', 'name':'Las Palmas', 'state_name':'Canarias'])
        provinces.add(['province_id':'36', 'name':'Pontevedra', 'state_name':'Galicia'])
        provinces.add(['province_id':'26', 'name':'La Rioja', 'state_name':'La Rioja'])
        provinces.add(['province_id':'37', 'name':'Salamanca', 'state_name':'Castilla y León'])
        provinces.add(['province_id':'38', 'name':'Santa Cruz de Tenerife', 'state_name':'Canarias'])
        provinces.add(['province_id':'40', 'name':'Segovia', 'state_name':'Castilla y León'])
        provinces.add(['province_id':'41', 'name':'Sevilla', 'state_name':'Andalucía'])
        provinces.add(['province_id':'42', 'name':'Soria', 'state_name':'Castilla y León'])
        provinces.add(['province_id':'43', 'name':'Tarragona', 'state_name':'Cataluña'])
        provinces.add(['province_id':'44', 'name':'Teruel', 'state_name':'Aragón'])
        provinces.add(['province_id':'45', 'name':'Toledo', 'state_name':'Castilla-La Mancha'])
        provinces.add(['province_id':'46', 'name':'Valencia', 'state_name':'Comunidad Valenciana'])
        provinces.add(['province_id':'47', 'name':'Valladolid', 'state_name':'Castilla y León'])
        provinces.add(['province_id':'48', 'name':'Vizcaya', 'state_name':'País Vasco'])
        provinces.add(['province_id':'49', 'name':'Zamora', 'state_name':'Castilla y León'])
        provinces.add(['province_id':'50', 'name':'Zaragoza', 'state_name':'Aragón'])

        saveProvinces(provinces);
    }

    def slurpTowns() {
        def towns = slurpUri(townUri, processTownFeed);
        saveTowns(towns);
    }

    def slurpUri(uri, parser) {
        HttpClient client = new HttpClient(connectionManager);
        HttpMethod method = new GetMethod(uri);

        def towns = null
        try {
            def status = client.executeMethod(method);
            if (status == HttpStatus.SC_OK) {
                def stream = method.getResponseBodyAsStream();
                towns = parser(stream);
            }
            else {
                log.error("Something went bad when slurping the towns list from ${uri} (${status})");
                throw new RuntimeException("Cannot slurp towns list from ${uri} (${status})")
            }
        }
        finally {
            method.releaseConnection();
        }
        return towns;
    }

    def processStateFeed = { stream ->
        def slurper = new XmlSlurper(new org.ccil.cowan.tagsoup.Parser());
        def root = slurper.parse(stream);
        def nodes = root.'**'.findAll{it.@summary.text() =='maquetacion'};

        def states = [];

        nodes[0].'**'.findAll{it.name() == 'td'}.each() {
            def state = [:]
            state['state_id'] = null;
            state['name'] = it.text();

            states.add(state);
        }

        return states;
    }

    def processProvinceFeed = { stream ->
        def slurper = new XmlSlurper(new org.ccil.cowan.tagsoup.Parser());
        def root = slurper.parse(stream);
        def nodes = root.'**'.findAll{it.@id.text() =='ir'};

        def provinces = [];

        nodes[0].'**'.findAll{it.name() == 'option'}.each() {
            if (it.@value.text()) {
                def value = it.@value.text();
                def province = [:]
                province['province_id'] = value.substring(value.size() - 6, value.size() - 4);
                province['name'] = it.text();

                provinces.add(province);
            }
        }

        return provinces;
    }

    def processTownFeed = { stream ->
        Workbook wb = new HSSFWorkbook(stream);
        Sheet sheet = wb.getSheetAt(0);

        def towns = []
        def curr = 0;
	for (Row row : sheet) {
            // Filter the headers of the excel file
            if (curr < 2) {
                log.warn("Discarding non-data cell");
                curr++;
                continue;
            }

            def town = [:]
            town['province_id'] = row.getCell(0).getStringCellValue().toInteger();
            town['town_id'] = row.getCell(1).getStringCellValue().toInteger();
            town['name'] = row.getCell(2).getStringCellValue();

            towns.add(town);
	}

        return towns;
    }

    def saveStates(states) {
        states.each() {
            Town town = new Town();
            town.gov_id = it['state_id'];
            town.name = it['name'];
            town.type = "state";

            Town country = Town.findByNameAndType('España', 'country');
            if (country == null) {
                country = new Town('name' : 'España', 'gov_id' : "0", "type" : "country");
                country.save(true);
            }
            town.parent = country;

            town.save(true);

            log.debug("State ${town.name} stored");
        };
    }

    def saveProvinces(provinces) {
        provinces.each() {
            Town town = new Town();
            town.gov_id = it['province_id'];
            town.name = it['name'];
            town.type = "province";

            Town state = Town.findByNameAndType(it['state_name'], "state");
            if (state != null) {
                town.parent = state;
            }

            town.save(true);

            log.debug("Province ${town.name} stored");
        };
    }

    def saveTowns(towns) {
        towns.each() {
            Town town = new Town();
            town.gov_id = it['town_id'];
            town.name = it['name'];
            town.type = "town";

            Town province = Town.findByGov_idAndType(it['province_id'], "province");
            if (province != null) {
                town.parent = province;
            }

            town.save(true);

            log.debug("Town ${town.name} stored");
        };
    }
}
